package com.pdweb.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.pdweb.vo.Cargos;

public class CargosDao extends DBconnection {

	private Connection conn;

	public CargosDao() throws ClassNotFoundException, SQLException {
		this.conn = getConnection();
	}

	public CargosDao(Connection conn) {
		this.conn = conn;
	}

	public void adicionaCargo(Cargos cargo) throws SQLException {

		PreparedStatement st = null;

		try {
			st = conn
					.prepareStatement("insert into cargos(st_nome_carg) value(?);");

			st.setString(1, cargo.getNomeCargo());
			st.execute();
		} finally {
			st.close();
		}
	}

	public List<Cargos> obterCargos() throws SQLException {

		List<Cargos> cargos = new ArrayList<Cargos>();

		Statement st = null;
		ResultSet rs = null;

		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from cargos");
			while (rs.next()) {
				Cargos result = new Cargos();
				result.setIdCargo(rs.getInt("id_cargo_carg"));
				result.setNomeCargo(rs.getString("st_nome_carg"));

				cargos.add(result);
			}
		} finally {
			st.close();
			rs.close();
		}
		return cargos;
	}

	public void excluirCargo(Cargos cargo) throws SQLException {

		PreparedStatement st = null;

		try {
			st = conn
					.prepareStatement("delete from cargos where id_cargo_carg = ?;");

			st.setInt(1, cargo.getIdCargo());
			st.execute();
		} finally {
			st.close();
		}
	}

	public Cargos obterCargo(Cargos cargo) throws SQLException {
		Cargos retorno = null;
		Statement st = null;
		ResultSet rs = null;

		try {
			st = conn.createStatement();

			String sql = "select * from cargos where id_cargo_carg = "
					+ cargo.getIdCargo();

			rs = st.executeQuery(sql);

			if (rs.next()) {
				retorno = new Cargos();

				retorno.setIdCargo(rs.getInt("id_cargo_carg"));
				retorno.setNomeCargo(rs.getString("st_nome_carg"));
			}
			return retorno;
		} finally {
			st.close();
			rs.close();
		}
	}

	public void atualizaCargo(Cargos cargo) throws SQLException {

		PreparedStatement st = null;
		try {
			st = conn
					.prepareStatement("UPDATE cargos SET st_nome_carg = ? WHERE id_cargo_carg = ?;");
			st.setString(1, cargo.getNomeCargo());
			st.setInt(2, cargo.getIdCargo());
			st.execute();
		} finally {
			st.close();

		}
	}

	public List<Cargos> consultaCargo(String sql) throws SQLException {

		List<Cargos> retorno = new ArrayList<Cargos>();
		Statement st = null;
		ResultSet rs = null;

		try {
			st = conn.createStatement();

			rs = st.executeQuery(sql);

			
				while (rs.next()) {
					
					Cargos cargos = new Cargos();
					cargos.setIdCargo(rs.getInt("id_cargo_carg"));
					cargos.setNomeCargo(rs.getString("st_nome_carg"));

					retorno.add(cargos);
				
			}
			return retorno;
		} finally {
			st.close();
			rs.close();
		}
	}
}
